create database LibraryManagement
go
use LibraryManagement
go
create table UserInfo(
	userid int identity(1000,10) primary key,--用户id
	username varchar(20) not null,--用户名
	userpwd varchar(40) not null,--用户密码
	realname varchar(40),--真实姓名
	usertype int --用户类型
)
create table BookInfo(
	bookid varchar(13) primary key,--图书id
	bookname varchar(50) not null,--图书名称
	stocknum int,--图书数量
	price decimal(6,2),--图书价格
	publishdate datetime,--出版日期
	author varchar(50),--作者
	booktype varchar(20),--图书类别
	publisher varchar(50)--出版社
)
create table ReaderInfo(
	readerid varchar(20) primary key,--读者id
	readername varchar(20) not null,--读者姓名
	sex char(2),--读者性别
    birthday datetime,--出生日期
	tel varchar(22)--联系电话
)
create table LendingInfo(
	id int identity(100,10) primary key,--借阅记录id 
	readerid varchar(20) not null, --读者id
	bookid varchar(40) not null,--图书id
	lendingdate datetime not null, --借阅日期
	returndate datetime, --归还日期
	userid int --操作人id
)
go
create view v_lendinginfo
as
select id,lendingInfo.bookid,bookname,booktype,lendingInfo.readerid,
readername,tel,lendingdate,returndate,realname from lendingInfo join bookinfo
on bookinfo.bookid = lendinginfo.bookid join readerinfo
on lendinginfo.readerid = readerinfo.readerid join userinfo
on lendinginfo.userid = userinfo.userid
go
insert into UserInfo(username,userpwd,realname,usertype) values('admin','e10adc3949ba59abbe56e057f20f883e','系统管理员',1);